import pandas as pd
from sqlalchemy import create_engine
import matplotlib.pyplot as plt
import logging

# ---- 中文字体设置 ----
plt.rcParams['font.sans-serif'] = ['SimHei']  # 设置中文黑体
plt.rcParams['axes.unicode_minus'] = False   # 解决负号'-'显示为方块问题

# 日志配置
logging.basicConfig(filename='insurance_metric.log', level=logging.INFO,
                    format='%(asctime)s - %(levelname)s - %(message)s')

# 数据库连接
engine = create_engine("mysql+pymysql://root:123456@localhost:3306/test")

# 1. 读取原始保单数据
df = pd.read_sql("SELECT * FROM insurance_policies", engine)

# 2. 计算已赚保费
df["earned_premium"] = df["original_premium"] - df["ceded_premium"] - df["uepr"] + df["reversed_uepr"]

# 3. 计算各业务指标，保留两位小数
df["赔付率"] = round((df["claim_payment"] + df["pending_claims"] - df["reversed_pending_claims"]) / df["earned_premium"] * 100, 2)
df["综合费用率"] = round((df["admin_expense"] + df["commission"] + df["reinsurance_fee"] + df["tax"] - df["reinsurance_fee_reversal"]) / df["earned_premium"] * 100, 2)
df["综合成本率"] = round((df["claim_payment"] + df["pending_claims"] - df["reversed_pending_claims"] +
                      df["admin_expense"] + df["commission"] + df["reinsurance_fee"] + df["tax"] -
                      df["reinsurance_fee_reversal"]) / df["earned_premium"] * 100, 2)
df["保费费用率"] = round(df["admin_expense"] / df["original_premium"] * 100, 2)
df["手续费及佣金比率"] = round(df["commission"] / df["original_premium"] * 100, 2)
df["分保费用比率"] = round(df["reinsurance_fee"] / df["ceded_premium"] * 100, 2)

# 4. 控制台打印各指标的平均值
mean_metrics = df[["赔付率", "综合费用率", "综合成本率", "保费费用率", "手续费及佣金比率", "分保费用比率"]].mean()
print("📊 业务指标平均值：")
print(mean_metrics)

# 5. 记录日志
logging.info("业务指标计算完成，平均指标如下：\n%s", mean_metrics.to_string())

# 6. 简单可视化示例：各指标的分布直方图
plt.figure(figsize=(15, 10))

指标列表 = ["赔付率", "综合费用率", "综合成本率", "保费费用率", "手续费及佣金比率", "分保费用比率"]

for i, 指标 in enumerate(指标列表, 1):
    plt.subplot(2, 3, i)
    plt.hist(df[指标].dropna(), bins=20, color='skyblue', edgecolor='black')
    plt.title(f"{指标}分布")
    plt.xlabel("百分比 (%)")
    plt.ylabel("保单数量")
    plt.grid(True)

plt.tight_layout()
plt.show()
